RCW Presentation

Presentation
Author

Ian Gray

Published

December 1, 2022

Poster

Here are some useful links and examples for a more indepth look into DuckDB.

Proven Speed in Analysis

Describtion of DuckDB by one of its Creator’s

Code Example’s

Here is a demonstration of the usefulness of DuckDB

R

building_the_database.r
library("DBI")
library("tidyverse")

Magic_DB = dbConnect(duckdb::duckdb(), dbdir = ":memory:", read_only = FALSE)

dbExecute(Magic_DB, "CREATE SCHEMA IF NOT EXISTS MTG;")
dbExecute(Magic_DB, "CREATE OR REPLACE TABLE MTG.Cards AS SELECT * FROM read_csv_auto('..\\MTG_Database\\data\\sqllite_data\\AllPrintingsCSVFiles\\cards.csv',SAMPLE_SIZE=-1);")
dbExecute(Magic_DB, "CREATE OR REPLACE TABLE MTG.Legalities AS SELECT * FROM read_csv_auto('..\\MTG_Database\\data\\sqllite_data\\AllPrintingsCSVFiles\\legalities.csv',SAMPLE_SIZE=-1);")
dbExecute(Magic_DB, "CREATE OR REPLACE TABLE MTG.Meta AS SELECT * FROM read_csv_auto('..\\MTG_Database\\data\\sqllite_data\\AllPrintingsCSVFiles\\meta.csv',SAMPLE_SIZE=-1);")
dbExecute(Magic_DB, "CREATE OR REPLACE TABLE MTG.Rulings AS SELECT * FROM read_csv_auto('..\\MTG_Database\\data\\sqllite_data\\AllPrintingsCSVFiles\\rulings.csv',SAMPLE_SIZE=-1);")
dbExecute(Magic_DB, "CREATE OR REPLACE TABLE MTG.Set_translations AS SELECT * FROM read_csv_auto('..\\MTG_Database\\data\\sqllite_data\\AllPrintingsCSVFiles\\set_translations.csv',SAMPLE_SIZE=-1);")
dbExecute(Magic_DB, "CREATE OR REPLACE TABLE MTG.Sets AS SELECT * FROM read_csv_auto('..\\MTG_Database\\data\\sqllite_data\\AllPrintingsCSVFiles\\sets.csv',SAMPLE_SIZE=-1);")
dbExecute(Magic_DB, "CREATE OR REPLACE TABLE MTG.Tokens AS SELECT * FROM read_csv_auto('..\\MTG_Database\\data\\sqllite_data\\AllPrintingsCSVFiles\\tokens.csv',SAMPLE_SIZE=-1);")

con <- dbGetQuery(Magic_DB, "SELECT * FROM MTG.Sets LIMIT 10")
View(con)
con_1 <- dbGetQuery(Magic_DB, "DESCRIBE MTG.Cards;")
View(con_1)
con_2 <- dbGetQuery(Magic_DB, "DESCRIBE MTG.Sets;")
View(con_2) 


con_3 <- dbGetQuery(Magic_DB, "SELECT s.code, c.rarity, s.name, c.name FROM MTG.Cards c JOIN MTG.Sets s ON c.setcode = s.code LIMIT 50")
View(con_3)

dbDisconnect(Magic_DB, shutdown = TRUE)

Python

import duckdb
import numpy as np
from pyarrow import json
import pandas as pd

con = duckdb.connect(database = ':memory:', read_only=False)

con.execute("CREATE OR REPLACE TABLE Cards AS SELECT * FROM read_csv_auto('..\data\sqllite_data\AllPrintingsCSVFiles\cards.csv',SAMPLE_SIZE=-1);")
con.execute("CREATE OR REPLACE TABLE Legalities AS SELECT * FROM read_csv_auto('..\data\sqllite_data\AllPrintingsCSVFiles\legalities.csv',SAMPLE_SIZE=-1);")
con.execute("CREATE OR REPLACE TABLE Meta AS SELECT * FROM read_csv_auto('..\data\sqllite_data\AllPrintingsCSVFiles\meta.csv',SAMPLE_SIZE=-1);")
con.execute("CREATE OR REPLACE TABLE Rulings AS SELECT * FROM read_csv_auto('..\data\sqllite_data\AllPrintingsCSVFiles\\rulings.csv',SAMPLE_SIZE=-1);")
con.execute("CREATE OR REPLACE TABLE Set_translations AS SELECT * FROM read_csv_auto('..\data\sqllite_data\AllPrintingsCSVFiles\set_translations.csv',SAMPLE_SIZE=-1);")
con.execute("CREATE OR REPLACE TABLE Sets AS SELECT * FROM read_csv_auto('..\data\sqllite_data\AllPrintingsCSVFiles\sets.csv',SAMPLE_SIZE=-1);")
con.execute("CREATE OR REPLACE TABLE Tokens AS SELECT * FROM read_csv_auto('..\data\sqllite_data\AllPrintingsCSVFiles\\tokens.csv',SAMPLE_SIZE=-1);")

df = con.execute("SELECT * FROM Cards").fetchdf()
df.info()

con.close()

GitHub Repository

Magic_Database